package com.test.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;

public class DBUtils {
	private static String USER_INFO = "USER_INFO";
	private static String MESSAGE = "MESSAGE";

	static Connection getConnection() throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		// 加载驱动
		Properties props = new Properties();
		props.put("user", "test");
		props.put("password", "test");
		return DriverManager.getConnection("jdbc:derby:liuyan;create=true",
				props);// 连接数据库
	}

	/**
	 * 建表
	 */
	public void createTable() {
		try {
			Connection conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String createSql = "create table " + this.USER_INFO + " "
					+ "(ID INT generated by default as identity,"
					+ "NAME VARCHAR(10) NOT NULL,"
					+ "pass varchar(20) not null)";
			System.out.println(createSql);
			st.execute(createSql);
			// 建表
			System.out.println("table " + this.USER_INFO + " created !");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 建表 message
	 */
	public void createTableMessage() {
		try {
			Connection conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String createSql = "create table " + this.MESSAGE + " "
					+ "(ID INT generated by default as identity ,"
					+ "uid int,"
					+ "leavetime date DEFAULT NULL,"
					+ "content varchar(32672) DEFAULT NULL," +
					"primary key (ID))";
			System.out.println(createSql);
			st.execute(createSql);
			// 建表
			System.out.println("table " + this.MESSAGE + " created !");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 添加用户
	 * @param name
	 * @param pass
	 */
	public void addUser(String name, String pass) {
		Connection conn;
		try {
			conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String insert = "insert into " + this.USER_INFO
			+ "(NAME,pass) values ('" + name + "','" + pass + "')";
			System.out.println("sql : " + insert);
			st.executeUpdate(insert);
			System.out.println("insert ok!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 判断用户 是否存在
	 * @param name
	 * @param pass
	 */
	public boolean exitUser(String name, String pass, User user) {
		Connection conn;
		try {
			conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String sql = "select * from " + this.USER_INFO
			+ " where NAME='" + name + "' and pass='" + pass + "'";
			System.out.println("sql : " + sql);
			ResultSet rs = st.executeQuery(sql);
			if( rs.next()) {
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				return true;
			}
			else 
				return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 打印用户
	 */
	public void printUser() {
		Connection conn;
		try {
			conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String sql = "select * from " + this.USER_INFO;
			System.out.println("sql : " + sql);
			ResultSet rs = st.executeQuery(sql);// 读取刚插入的数据
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String para = rs.getString(3);
				System.out.print("ID = " + id);
				System.out.print(" ;name = " + name);
				System.out.println(" ;para = " + para);
			}
			//System.out.println("insert ok!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 添加留言
	 * @param context
	 * @param uid
	 */
	public void addMessage(String context, int uid){
		Connection conn;
		try {
			conn = DBUtils.getConnection();
			String sql = "insert into " + this.MESSAGE
			+ "(uid, leavetime, content) values (?,?,?)";
			PreparedStatement st = conn.prepareStatement(sql);
			st.setInt(1, uid);
			st.setDate(2, new java.sql.Date(new Date().getTime()));
			st.setString(3, context);
			System.out.println("sql : " + sql);
			st.executeUpdate();
			System.out.println("insert ok!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 打印留言
	 */
	public void printMessage() {
		Connection conn;
		try {
			conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			String sql = "select " +this.USER_INFO + ".name, " +
					this.MESSAGE + ".content, " +
					this.MESSAGE + ".leavetime" +
					" from " + this.MESSAGE + "," + this.USER_INFO + 
			" where " + this.MESSAGE + ".uid = " + this.USER_INFO + ".id";
			System.out.println("sql : " + sql);
			ResultSet rs = st.executeQuery(sql);// 读取刚插入的数据
			while (rs.next()) {
				String name = rs.getString(1);
				String content = rs.getString(2);
				Date date = rs.getDate(3);
				System.out.print("name = " + name);
				System.out.print(" ;date = " + date);
				System.out.println(" ;content = " + content);
			}
			//System.out.println("insert ok!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		//new DBUtils().createTable();
		//new DBUtils().addUser("李明", "12345");
		//new DBUtils().printUser();
		//new DBUtils().createTableMessage();
		//new DBUtils().addMessage("你好你好你好", 1);
		//new DBUtils().printMessage();
		User u = new User();
		System.out.println(new DBUtils().exitUser("李明", "12345", u));
		System.out.println(u.getId() + ":" + u.getUsername());
	}
}
